<?php
/**
 * Builder for Db_object
 * @package Db
 * @subpackage Db_Object
 * @author Kirill A Egorov kirill.a.egorov@gmail.com 
 * @copyright Copyright (C) 2011-2012  Kirill A Egorov, 
 * DVelum project http://code.google.com/p/dvelum/ , http://dvelum.net
 * @license General Public License version 3
 */
class Db_Object_Builder{
    
	static private $_instance = null;
	/**
	 * @var Zend_Db_Adapter_Mysqli
	 */
    protected $_db;
    
    protected $_dbPrefix;
    
    static protected $_writeLog = false;
    
    static protected $_logPrefix = '0.1';
    
    static protected $_logsPath = './logs/';
    
    protected function __construct(){
         $this->_db = Db_Object::getDbConnection();
         $this->_dbPrefix = Db_Object::getDbPrefix();
    }
    protected function __clone(){}
    /**
     * @return Db_Object_Builder
     */
    static public function getInstance(){
        if(is_null(self::$_instance))
            self::$_instance = new self();
            
        return self::$_instance;    
    }

    static public $numTypes = array('tinyint','smallint','mediumint','int','bigint','float','double','decimal','bit');
    static public $intTypes = array('tinyint','smallint','mediumint','int','bigint','bit');
    static public $floatTypes = array('decimal' ,'float' ,'double');
    static public $charTypes = array('char','varchar');
    static public $textTypes = array('tinytext','text','mediumtext','longtext'); 
    static public $dateTypes = array('date','datetime','time','timestamp'); 
    
    /**
     * Write SQL log
     * @param boolean $flag
     */
    static public function writeLog($flag){
    	self::$_writeLog = (boolean) $flag;
    }
    /**
     * Set query log file prefix
     * @param string $string
     */
    static public function setLogPrefix($string){
    	self::$_logPrefix = strval($string);
    }
    /**
     * Set logs path
     * @param string $string
     */
    static public function setLogsPath($string){
    	self::$_logsPath = $string;
    }
    /**
     * Log queries
     * @param string $sql
     */
    protected function _logSql($sql){
    	if(!self::$_writeLog)
    		return;
    	$str = "\n--\n--".date('Y-m-d H:i:s')."\n--\n" .$sql;
    	file_put_contents(self::$_logsPath.self::$_logPrefix, $str , FILE_APPEND);
    }
    /**
     * Check if DB table has correct structure
     * @param Db_Object $object
     * @return boolean
     */
    public function validate(Db_Object $object){

        if(!$this->tableExists($object->getConfig()->get('table')))     
            return false;
            
        $updateColumns = $this->prepareColumnUpdates($object);
        $updateIndexes = $this->prepareIndexUpdates($object);
        
        if(!empty($updateColumns) || !empty($updateIndexes))
            return false;
        else 
            return true;
    }
    /**
     * Prepare list of columns to be updated
     * @param Db_Object $object
     * @return array  (
     * 	 'name'=>'somename',
     *   'action'=>[drop/add/change], 
     * )
     */
    public function prepareColumnUpdates(Db_Object $object){
         $updates = array();
        
         $config = $object->getConfig()->__toArray();
        	
         if(!$this->tableExists($config['table']))      
         	$fields = array();
         else 
         	$fields = $this->_getExistingColumns($this->_dbPrefix . $config['table']);

         /*
          * Remove deprecated fields
          */
          foreach ($fields as $k=>$v)
          	 if(!array_key_exists($k , $config['fields']))
          	     $updates[] = array('name'=>$k,'action'=>'drop','type'=>'field'); 
          	           
         foreach ($config['fields'] as $name=>$v){
         			
         	/*
             * Add new field
             */
             if(!array_key_exists($name , $fields)){
				$updates[] = array('name'=>$name,'action'=>'add');
                continue;
         	}
             
             $dataType = strtolower($fields[$name]['DATA_TYPE']);            
             /*
              * Field type compare flag
              */
             $typeCmp = false;  
             /*
              * Field length compare flag
              */
             $lenCmp = false;
             /*
              * IsNull compare flag
              */
             $nullcmp = false;
             /*
              * Default value compare flag
              */
             $defaultCmp = false;
             /*
              * Unsigned compare flag
              */
             $unsignedCmp = false;
             /**
              * AUTO_INCREMENT compare flag
              * @var bool
              */
             $incrementCmp = false;
                            
             if($v['db_type'] === 'boolean' && $dataType ==='tinyint'){
                 /*
                  * skip check for booleans
                  */
             }else{
             	
             	if(strtolower($v['db_type']) !== $dataType)
                    $typeCmp = true;     
             		
                if(in_array($v['db_type'], self::$floatTypes , true)){
                  	 if($v['db_scale']!=$fields[$name]['SCALE'] || $v['db_precision']!=$fields[$name]['PRECISION'] )
                  		$lenCmp = true;                  		
                }else{
                  	 if(!in_array($v['db_type'], self::$numTypes , true) && isset($v['db_len']))
                  	 	$lenCmp = (string) $v['db_len'] != (string) $fields[$name]['LENGTH'];                	 
                }   
                   
                 $nullcmp = (boolean) $v['db_isNull'] !== (boolean) $fields[$name]['NULLABLE'];
	                                     
			     if(!isset($v['db_unsigned']) && $fields[$name]['UNSIGNED'])
			             $unsignedCmp = true;
			                                     
			     if(isset($v['db_unsigned'])  &&  $v['db_unsigned']  && !$fields[$name]['UNSIGNED'])
			             $unsignedCmp = true;
             }
             
	        if(!isset($v['db_default']) && strlen($fields[$name]['DEFAULT']) && !in_array($v['db_type'], self::$dateTypes, true))
	       		$defaultCmp = true;
	       
	       	if(isset($v['db_default']) && !in_array($v['db_type'], self::$dateTypes, true))
		     	$defaultCmp = (string) $v['db_default']!=(string) $fields[$name]['DEFAULT'];
		     	
			if ($fields[$name]['IDENTITY'] && $name != 'id')
				$incrementCmp = true;				
		     
			if ($name == 'id' && !$fields[$name]['IDENTITY'])
				$incrementCmp = true;
		     /*
		      * If not passed at least one comparison then rebuild the the field
		      */
		     if($typeCmp || $lenCmp || $nullcmp || $defaultCmp || $unsignedCmp || $incrementCmp)
		         $updates[] = array('name'=>$name,'action'=>'change');
         }
         return $updates;
    }
    /**
     * Rename object field
     * @param Db_Object_Config $objectConfig
     * @param string $oldName
     * @param string $newName
     * @return boolean 
     */
    public function renameField(Db_Object_Config $objectConfig , $oldName , $newName)
    {    		
    	$fieldConfig = $objectConfig->getFieldConfig($newName);
    	$sql = ' ALTER TABLE '.$objectConfig->getTable().' CHANGE `'.$oldName.'` ' . $this->_proppertySql($newName, $fieldConfig);
    	try{
    		$this->_db->query($sql);
    		$this->_logSql($sql);
    		return true;
    	}catch (Exception $e){
    		return false;
    	}
    }
    
    /**
     * Create / alter db table
     * @param Db_Object $object
     * @return void
     */
    public function build(Db_Object $object)
    {
        /*
         * Create table if not exists
         */
        if(!$this->tableExists($object->getConfig()->get('table')))
        {  
        	$sql = $this->_sqlCreate($object);
            $this->_db->query($sql);  
            $this->_logSql($sql);
            return;
        }  
         
        $colUpdates = $this->prepareColumnUpdates($object);
        $indexUpdates = $this->prepareIndexUpdates($object);
        
        /*
         * Update comands
         */
        $cmd = array();
                
        if(!empty($colUpdates)){           
            $fieldsConfig = $object->getConfig()->getFieldsConfig();  
            foreach ($colUpdates as $info){
                
                switch ($info['action']){
                    case 'drop' :    $cmd[] = "\n".'DROP `' . $info['name'] . '`'; 
                        break;
                    case 'add'	:    $cmd[] = "\n".'ADD ' . $this->_proppertySql($info['name'],$fieldsConfig[$info['name']]);
                        break;
                    case 'change':   $cmd[] = "\n".'CHANGE `' . $info['name'] . '`  '.$this->_proppertySql($info['name'] , $fieldsConfig[$info['name']]);  
                        break;
                }            
            }
        }
        
        if(!empty($indexUpdates)){
            $indexConfig = $object->getConfig()->getIndexesConfig();
            
            foreach ($indexUpdates as $info){
                 switch ($info['action']){
                    case 'drop' :    
                        if ($info['name'] == 'PRIMARY')
                            $cmd[] = "\n".'DROP PRIMARY KEY';
                        else 
                            $cmd[] = "\n".'DROP INDEX ' . $info['name'];                       
                        break;
                    case 'add'	:  
                            $cmd[] = $this->_prepareIndex($info['name'] ,  $indexConfig[$info['name']] , $object);                       
                        break;
                }   
            }
        }

        
        if(!empty($cmd)){
        	$sql = 'ALTER TABLE `' . $object->getTable() .'` '.implode(',', $cmd).';';
        	$this->_db->query($sql);
        	$this->_logSql($sql);
        }
    }
    /**
     * Build indexes for create query
     * @param Db_Object $object
     * @return array - sql parts
     */
    protected function _createIndexes(Db_Object $object){
    	$objConfig = $object->getConfig();
    	$cmd = array();
    	$configIndexes = $objConfig->getIndexesConfig();
    	
    	foreach ($configIndexes as $index=>$config)							
    		$cmd[] = $this->_prepareIndex($index , $config , $object , true);
    			
    	return $cmd;
    }
    /**
     * Prepare list of indexes to be updated
     * @param Db_Object $object
     * @return array  (
     * 	 'name'=>'indexname',
     *   'action'=>[drop/add], 
     * )
     */
    public  function prepareIndexUpdates(Db_Object $object){
        $updates = array();
    	/*
    	 * Get object config
    	 */
    	$objConfig = $object->getConfig();	
    	/*
    	 * Get indexes form database table
    	 */
    	$indexes = $this->_db->fetchAll('SHOW INDEX FROM '.$object->getTable());
    	$realIndexes = array();
    	
    	if(empty($indexes))
    		return array();
    		
    	foreach ($indexes as $k=>$v) {
         		  		
    	    $isFulltext = (boolean)($v['Index_type'] === 'FULLTEXT');
    			
    	    if(!isset($realIndexes[$v['Key_name']]))
    	   		$realIndexes[$v['Key_name']]=array('columns'=>array() ,'fulltext'=>$isFulltext , 'unique'=>(boolean)(!$v['Non_unique']));		
    			
    		$realIndexes[$v['Key_name']]['columns'][] = $v['Column_name'];	
    	}	
    	/*
    	 * Get indexes from object config
    	 */ 
    	$configIndexes = $objConfig->getIndexesConfig();
    	$cmd = array();
    	/*
    	 * Drop invalid indexes
    	 */
    	foreach ($realIndexes as $index => $conf) {
    		if(!array_key_exists((string)$index, $configIndexes))    
    			    $updates[] = array('name'=>$index,'action'=>'drop');
    	}   			
    	/*
    	 * Compare DB and Config indexes, 
    	 * create if not exist, 
    	 * drop and create if invalid
    	 */  	
    	if(!empty($configIndexes)){
    		foreach ($configIndexes as $index=>$config){
    			if(!array_key_exists((string)$index, $realIndexes)){  	
    			    $updates[] = array('name'=>$index,'action'=>'add'); 	
    			}else{
    				if(!$this->_isSameIndexes($config , $realIndexes[$index])){
    					$updates[] = array('name'=>$index,'action'=>'drop'); 
    					$updates[] = array('name'=>$index,'action'=>'add'); 
    				}
    			}
    		}
    	}
    	return $updates;
    }
    /**
     * Compare existed index and its system config
     * @param array $cfg1
     * @param array $cfg2
     * @return boolean
     */
    protected function _isSameIndexes(array $cfg1 , array $cfg2){
    	$colDiff = array_diff($cfg1['columns'], $cfg2['columns']);
    	if($cfg1['fulltext'] !== $cfg2['fulltext'] || $cfg1['unique']!==$cfg2['unique'] || !empty($colDiff))
    		return false;
    		
    	return true;	
    }
    /**
     * Prepare Add INDEX command
     * @param string $index
     * @param array $config
     * @param boolean $create - optional use create table mode
     * @param Db_Object $object
     * @return string
     */
    protected  function _prepareIndex($index , array $config , Db_Object $object , $create = false){
    	
	   if(isset($config['primary']) && $config['primary']){  	
	       
	        if(!isset($config['columns'][0]))
				trigger_error('Invalid index config');
				
			if($create)
    			return "\n".' PRIMARY KEY (`'.$config['columns'][0].'`)';
    		else 
    			return "\n".' ADD PRIMARY KEY (`'.$config['columns'][0].'`)';
		}
		
		$createType ='';
		/*
		 * Set key length for text column index 
		 */
		foreach ($config['columns'] as &$col){
		    if($object->getConfig()->isText($col))
		        $col = '`'.$col.'`(32)';
		    else 
		        $col = '`'.$col.'`';
		}unset($col);
		
		$str = '`'.$index.'` ('.implode(',', $config['columns']).')';

		if(isset($config['unique']) && $config['unique'])
			$createType = $indexType = 'UNIQUE';
		elseif(isset($config['fulltext']) && $config['fulltext'])
			$createType = $indexType = 'FULLTEXT';
		else 
			$indexType = 'INDEX';
		  			
		if($create)
			return "\n".' '.$createType.' KEY '.$str;
		else 
			return "\n".' ADD '.$indexType.' '.$str;
    }
 	/**
     * Get property SQL query
     * @param array $data
     * @return string
     */
    protected function _proppertySql($name , $data){
        $property = new Db_Object_Property($name);
        $property->setData($data);            
        return $property->__toSql();
    }
    /**
     * Get  SQL for table creation
     * @property string $table
     * @property array Db_Object
     * @return string
     */
    protected  function _sqlCreate( Db_Object $object){  
		$config = $object->getConfig();
        $fields = $config->get('fields');
        
        $sql = ' CREATE TABLE  `' . $this->_dbPrefix . $config->get('table') . '` (';
             
          if(empty($fields))
               throw new Exception('_sqlCreate :: empty properties');
          /*
           * Add columns
           */
          foreach ($fields as $k=>$v) 
                 $sql.= $this->_proppertySql($k,$v).' ,  '."\n";
    
          $indexes = $this->_createIndexes($object);
          /*
           * Add indexes
           */
          if(!empty($indexes))
          		$sql.=' ' . implode(', ', $indexes);
          
          $sql.="\n".') ENGINE='.$config->get('engine') .'  DEFAULT CHARSET=utf8 ;';  
                 
          return $sql;       
    }
    /**
     * Get Existing Columns
     * @return array
     */
    protected function _getExistingColumns($table){
        return $this->_db->describeTable($table); 
    }
    /**
     * Check if table exists
     * @param string $name - teble name without prefix
     * @return boolean
     */
    public function tableExists($name){
    	$tables = $this->_db->listTables();
    	return in_array($this->_dbPrefix.$name, $tables ,true);
    }
    /**
     * Rename database table
     * @param string $oldName
     * @param string $newName
     * @return boolean
     * @throws Exception
     */
    public function renameTable($oldName , $newName){
    	$store = Store_Local::getInstance();
    	$sql = 'RENAME TABLE `'.$store->get('dbConfig')->get('dbname').'`.`'.$this->_dbPrefix.$oldName.'` TO `'.$store->get('dbConfig')->get('dbname').'`.`'.$this->_dbPrefix.$newName.'` ;';
    	try {
    		$this->_db->query($sql);
    		$this->_logSql($sql);
    		return true;
    	}catch (Exception $e){
    		return false;
    	}
    }
    /**
     * Change Db table engine
     * @param string $table - table name without prefix
     * @param string $engine - new engine name
     * @return boolean
     * @throws Exception
     */
    public function changeTableEngine($table , $engine){
    	$sql = 'ALTER TABLE `'.$this->_dbPrefix.$table.'` ENGINE = '.$engine;
    	try {
    		$this->_db->query($sql);
    		$this->_logSql($sql);
    		return true;
    	} catch (Exception $e) {
    		return false;
    	}
    }
    /**
     * Remove object
     * @param string $name
     * @return boolean
     */
    public function remove($name){	
    	$sql = 'DROP TABLE `' . Db_Object_Config::getInstance($name)->getTable().'`';    	
    	try{
    		$this->_db->query($sql);
    		$this->_logSql($sql);
    		return true;
    	}catch (Exception $e){
    		return false;
    	}
    }
}
